package md.mru.core.dao;

import md.mru.core.domains.administration.TreeModelUserGroup;
import md.mru.core.dtos.ClasificationDto;
import md.mru.core.dtos.TreeNodeModelDto;
import md.mru.core.dtos.RootTreeModelDto;
import org.primefaces.model.TreeNode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author sergiu.marin
 * @since 15.04.2014.
 */
@Repository
public class TreeModelDao {

    @Autowired
    private DataSource dataSource;

    public RootTreeModelDto getGroupRootView(Set<Long> groupNodes) {
        return loadTreeModel(null, groupNodes, true);
    }

    public RootTreeModelDto getRootView() {
        return loadTreeModel(null, new HashSet<Long>(0), true);
    }


    private RootTreeModelDto loadTreeModel(Long dosarId, Set<Long> groupNodes, Boolean admin) {
        /*String loggedUser = "1";

        Connection con = null;
        TreeNodeModelDto obj;
        TreeNode root = null;
        RootTreeModelDto rootT = new RootTreeModelDto();
        //List<Integer> nodes = getUserNodes(TreeBean.getLoggedUser());
        *//*List<TreeModelUserGroup> grpNodes = null;
        if (groupID != null) {
            grpNodes = getNodesForGrp(groupID);
        }*//*

        try {
            List<TreeNode> stack = new ArrayList<TreeNode>();

            String query = "SELECT  tree_type as TRE_TPE"
                    + " , frs"
                    + " , lst"
                    + " , dsr_ste";
            if (admin) {
                query += " , 0 as DSR_ID";
            } else {
                query += " ,  folder_id as DSR_ID";
            }
            query += " , LPAD(' ',6*(LEVEL- 1)) || TO_CHAR( NAME) AS NMS"
                    + " , DESCRIPTION AS DSC , NAME AS nameX"
                    + " , LEVEL, source_.ID ID, phone_no_1 , phone_no_2 , phone_no_3     "
                    + " ,  PID, SYS_CONNECT_BY_PATH(source_.ID, ',') PARENTS"
                    + " , SYS_CONNECT_BY_PATH(NAME,' > ') parentsDSC     "
                    + " , CONNECT_BY_ISLEAF AS ISLEAF  , TPE_NME , TPE_ICO , TPE_ID , NOD_TPE , position as fvr , steDNME"
                    + " FROM "
                    + " ("
                    + "       SELECT T.*, lnk.*"
                    + "        from      "
                    + "         ( select tt.id,tt.pid,tt.phone_no_1 , tt.phone_no_2, tt.phone_no_3, tt.name, tt.description, tt.position,NF.ICON AS TPE_ICO , nf.name as TPE_NME, NF.TYPE AS TPE_ID , NF.ID AS NOD_TPE  from TREE_NODE_MODELS tt"
                    + "             LEFT JOIN FUNCTION_CLASSIFICATIONS NF ON TT.FNC_CLASSIFICATION_ID = NF.ID "
                    + "          WHERE tt.validate_from <= sysdate AND  sysdate <= NVL(tt.validate_to , sysdate+1)";

            if (!admin) {
                query += "             and (tt.id in (select tree_id from tree_user_groups where group_id in (select group_id from user_groups where user_id=(SELECT ID FROM USERS WHERE name = '" + loggedUser + "'))) or tt.id=0 )";
            }
            query += "          ) T"
                    + "          left join "
                    + "          ("
                    + "                 select tree_id,folder_id, folder.FIRST_NAME as frs, folder.LAST_NAME as lst, folder.folder_state_id AS dsr_ste, sted.name steDNME , sted.TREE_TYPE from  TREE_FOLDERS tf LEFT JOIN folders folder on folder.ID = tf.folder_id"
                    + " LEFT JOIN classification_states steD on steD.id = folder.folder_state_id"
                    + "                 WHERE ( SYSDATE >= tf.VALID_FROM AND SYSDATE < NVL(tf.VALID_TO , SYSDATE + 1) AND tf.state = 1)"
                    + "          ) lnk on lnk.tree_id=t.id"
                    + " )source_"
                    + " START WITH source_.ID = 0 "
                    + " CONNECT BY NOCYCLE PRIOR source_.ID = source_.PID "
                    + " ORDER SIBLINGS BY FVR , ID  ,DSR_STE ,  TRE_TPE";

            //List resultList = entityManagerFactory.createEntityManager().createNativeQuery(query).getResultList();
            con = dataSource.getConnection();

            PreparedStatement ps = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            boolean first = false;
            TreeNode lastTreeNode = null;

            while (rs.next()) {
                Integer treTpe = rs.getInt("tre_tpe");
                Long idX = rs.getLong("DSR_ID");

                if (lastTreeNode == null || (lastTreeNode.getData() != null && ((TreeNodeModelDto) lastTreeNode.getData()).getId() != rs.getInt("ID")) || (((TreeNodeModelDto) lastTreeNode.getData()).getId() == rs.getInt("ID") && !((TreeNodeModelDto) lastTreeNode.getData()).getFolderId().equals(idX) && (rs.getInt("dsr_ste") == 3 || rs.getInt("dsr_ste") == 7))) {
                    obj = new TreeNodeModelDto();
                    if (lastTreeNode != null && lastTreeNode.getData() != null && ((TreeNodeModelDto) lastTreeNode.getData()).getId() == rs.getInt("ID") && ((TreeNodeModelDto) lastTreeNode.getData()).getFolderId() != idX && (rs.getInt("dsr_ste") == 3 || rs.getInt("dsr_ste") == 7)) {
                        obj.setError(true);
                    }
                    obj.setId(rs.getInt("ID"));
                    obj.setParentId(rs.getInt("PID"));
                    obj.setName(rs.getString("nameX"));
                    obj.setDescription(rs.getString("DSC"));
                    obj.setLevel(rs.getInt("level"));
                    obj.setPath(rs.getString("parentsDsc"));
                    obj.setTpeId(rs.getInt("TPE_ID"));
                    obj.setTpeNme(rs.getString("TPE_NME"));
                    obj.setTpeIco(rs.getString("TPE_ICO"));
                    obj.setNodTpe(rs.getInt("NOD_TPE"));
                    obj.setPhoneNo(rs.getString("phone_no_1"));
                    obj.setPhoneNo2(rs.getString("phone_no_2"));
                    obj.setPhoneNo3(rs.getString("phone_no_3"));
                    obj.setPosition(rs.getDouble("FVR"));
                    obj.setSte(rs.getInt("dsr_ste"));


                    //if (rs.getInt("dsr_ste") == 3 || rs.getInt("dsr_ste") == 7) {
                    if (treTpe == 1) {
                        if (idX != null && idX != 0) {
                            obj.setFolderId(idX);
                            obj.setFirstName(rs.getString("FRS"));
                            obj.setLastName(rs.getString("LST"));
                        }
                    }

                    if (!first) {
                        if (obj.getId() == 0) {
                            root = new DefaultTreeNode(obj, null);
                            stack.add(root);
                        } else {
                            root = new DefaultTreeNode("ROOT", null);
                            stack.add(root);
                            TreeNode child = new DefaultTreeNode(obj, root);
                            stack.add(child);
                        }
                        first = true;
                        continue;
                    }

                    //int stakSize = stack.size();
                    if (((TreeNodeModelDto) ((DefaultTreeNode) stack.get(stack.size() - 1)).getData()).getLevel() < obj.getLevel()) {
                        lastTreeNode = new DefaultTreeNode(obj, (DefaultTreeNode) stack.get(stack.size() - 1));
                        if (groupNodes.contains(obj.getId())) {
                            lastTreeNode.setSelected(true);
                        }
                        stack.add(lastTreeNode);

                            *//*add *//*
                        //if (rs.getInt("dsr_ste") != 3 && rs.getInt("dsr_ste") != 7 && idX != null && idX != 0) {
                        if (treTpe != 1 && idX != null && idX != 0) {
                            obj = new TreeNodeModelDto();
                            obj.setId(rs.getInt("ID"));
                            obj.setFolderId(rs.getLong("DSR_ID"));
                            obj.setFirstName(rs.getString("FRS"));
                            obj.setLastName(rs.getString("LST"));
                            obj.setName(rs.getString("steDNME"));
                            obj.setTpeIco("ui-icon-person");
                            obj.setTpeId(rs.getInt("TPE_ID"));
                            obj.setSte(rs.getInt("dsr_ste"));
                            obj.setNodTpe(rs.getInt("NOD_TPE"));
                            obj.setStyleClass("maternityNode");
                            obj.setFolderStyleClass("inactivePerson");
                            TreeNode child = new DefaultTreeNode(obj, lastTreeNode); //
                        }


                        if (obj.getFolderId() != null && dosarId != null && obj.getFolderId().longValue() == dosarId.longValue()) {
                            lastTreeNode.setSelected(true);
                            rootT.setSelectedNode(lastTreeNode);
                            expandAllParents(lastTreeNode);
                        }

                        if (obj.getError()) {
                            propagateEroor(lastTreeNode);
                        }
                        continue;
                    }

                    if (((TreeNodeModelDto) ((DefaultTreeNode) stack.get(stack.size() - 1)).getData()).getLevel().equals(obj.getLevel())) {
                        if (stack.size() > 1) {
                            stack.remove(stack.size() - 1);
                        }
                        lastTreeNode = new DefaultTreeNode(obj, (DefaultTreeNode) stack.get(stack.size() - 1));
                        if (groupNodes.contains(obj.getId())) {
                            lastTreeNode.setSelected(true);
                        }
                        stack.add(lastTreeNode);
                            *//*add *//*
                        //if (rs.getInt("dsr_ste") != 3 && rs.getInt("dsr_ste") != 7 && idX != null && idX != 0) {
                        if (treTpe != 1 && idX != null && idX != 0) {
                            obj = new TreeNodeModelDto();
                            obj.setId(rs.getInt("ID"));
                            obj.setFolderId(rs.getLong("DSR_ID"));
                            obj.setFirstName(rs.getString("FRS"));
                            obj.setLastName(rs.getString("LST"));
                            obj.setName(rs.getString("steDNME"));
                            obj.setTpeIco("ui-icon-person");
                            obj.setTpeId(rs.getInt("TPE_ID"));
                            obj.setSte(rs.getInt("dsr_ste"));
                            obj.setNodTpe(rs.getInt("NOD_TPE"));
                            obj.setStyleClass("maternityNode");
                            obj.setFolderStyleClass("inactivePerson");
                            TreeNode child = new DefaultTreeNode(obj, lastTreeNode); //
                        }


                        if (obj.getFolderId() != null && dosarId != null && obj.getFolderId().longValue() == dosarId.longValue()) {
                            lastTreeNode.setSelected(true);
                            rootT.setSelectedNode(lastTreeNode);
                            expandAllParents(lastTreeNode);
                        }
                        if (obj.getError()) {
                            propagateEroor(lastTreeNode);
                        }

                        continue;
                    }

                    if (((TreeNodeModelDto) ((DefaultTreeNode) stack.get(stack.size() - 1)).getData()).getLevel() > obj.getLevel()) {
                        boolean bExist = true;
                        while (bExist) {
                            if (((TreeNodeModelDto) ((DefaultTreeNode) stack.get(stack.size() - 1)).getData()).getId() != obj.getParentId()) {
                                if (stack.size() == 1) {
                                    break;
                                }
                                stack.remove(stack.size() - 1);
                            } else {
                                break;
                            }
                        }
                        lastTreeNode = new DefaultTreeNode(obj, (DefaultTreeNode) stack.get(stack.size() - 1));
                        if (groupNodes.contains(obj.getId())) {
                            lastTreeNode.setSelected(true);
                        }
                        stack.add(lastTreeNode);

                            *//*add *//*
                        //if (rs.getInt("dsr_ste") != 3 && rs.getInt("dsr_ste") != 7 && idX != null && idX != 0) {
                        if (treTpe != 1 && idX != null && idX != 0) {
                            obj = new TreeNodeModelDto();
                            obj.setId(rs.getInt("ID"));
                            obj.setFolderId(rs.getLong("DSR_ID"));
                            obj.setFirstName(rs.getString("FRS"));
                            obj.setLastName(rs.getString("LST"));
                            obj.setName(rs.getString("steDNME"));
                            obj.setTpeIco("ui-icon-person");
                            obj.setTpeId(rs.getInt("TPE_ID"));
                            obj.setSte(rs.getInt("dsr_ste"));
                            obj.setNodTpe(rs.getInt("NOD_TPE"));
                            obj.setStyleClass("maternityNode");
                            obj.setFolderStyleClass("inactivePerson");
                            TreeNode child = new DefaultTreeNode(obj, lastTreeNode); //
                        }

                        if (obj.getFolderId() != null && dosarId != null && obj.getFolderId().longValue() == dosarId.longValue()) {
                            lastTreeNode.setSelected(true);
                            rootT.setSelectedNode(lastTreeNode);
                            expandAllParents(lastTreeNode);
                        }
                        if (obj.getError()) {
                            propagateEroor(lastTreeNode);
                        }
                        continue;
                    }
                } else {

                    if (!admin && lastTreeNode != null && lastTreeNode.getData() != null && ((TreeNodeModelDto) lastTreeNode.getData()).getId() == rs.getInt("ID")) {
                        obj = new TreeNodeModelDto();
                        obj.setId(rs.getInt("ID"));
                        obj.setFolderId(rs.getLong("DSR_ID"));
                        obj.setFirstName(rs.getString("FRS"));
                        obj.setLastName(rs.getString("LST"));
                        obj.setName(rs.getString("steDNME"));
                        obj.setTpeIco("ui-icon-person");
                        obj.setTpeId(rs.getInt("TPE_ID"));
                        obj.setSte(rs.getInt("dsr_ste"));
                        obj.setNodTpe(rs.getInt("NOD_TPE"));
                        obj.setStyleClass("maternityNode");
                        obj.setFolderStyleClass("inactivePerson");
                        TreeNode child = new DefaultTreeNode(obj, lastTreeNode); //

                        if (obj.getFolderId() != null && dosarId != null && obj.getFolderId().longValue() == dosarId.longValue()) {
                            child.setSelected(true);
                            rootT.setSelectedNode(child);
                            expandAllParents(child);
                        }

                    }
                }

            }
            rootT.setRoot(root);
            return rootT;
        } catch (Exception ex) {
            System.err.println("Eroare loadTreeModel: " + ex);
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    System.err.println("Eroare loadTreeModel: " + e);
                }
            }
        }*/
        return null;
    }

    private void expandAllParents(TreeNode tn) {
        if (tn.getParent() != null) {
            tn.setExpanded(true);
            expandAllParents(tn.getParent());
        }
    }

    private void propagateEroor(TreeNode tn) {
        if (tn.getParent() != null) {
           // ((TreeNodeModelDto) tn.getData()).setError(true);
            propagateEroor(tn.getParent());
        }
    }

    public List<TreeModelUserGroup> getNodesForGrp(long grpId) {
        List<TreeModelUserGroup> nodes = new ArrayList<TreeModelUserGroup>();
        Connection con = null;
        try {
            String query = "SELECT * FROM TREE_USER_GROUPS WHERE GROUP_ID = " + grpId;
            con = dataSource.getConnection();
            PreparedStatement ps = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                TreeModelUserGroup tg = null; //new TreeModelUserGroup(rs.getLong("TREE_ID"), rs.getLong("GROUP_ID"));
                tg.setCanWrite(rs.getBoolean("CAN_WRITE"));
                nodes.add(tg);
            }
            return nodes;
        } catch (Exception ex) {
            System.err.println("Eroare getNodesForGrp: " + ex);
            return nodes;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    System.err.println("Eroare finally getNodesForGrp: " + e);
                }
            }
        }
    }

    @Deprecated
    public List<ClasificationDto> getTreeTypes() {
        Connection con = null;
        try {
            String query = "SELECT NML_FNC.ID ID, NME_SRT||' > '||NML_FNC.NME NME "
                    + "FROM NML_FNc left join nml_fp on NML_FP.ID=nml_fnc.ctg order by nml_fnc.fvr, nml_fnc.ctg, nml_fnc.nme";
            con = dataSource.getConnection();
            PreparedStatement ps = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            List<ClasificationDto> tpes = new ArrayList<ClasificationDto>();
            while (rs.next()) {
                ClasificationDto tpe = new ClasificationDto();
                tpe.setId(rs.getInt("ID"));
                tpe.setName(rs.getString("NME"));
                tpes.add(tpe);
            }
            return tpes;
        } catch (Exception ex) {
            System.err.println("Eroare getTreeTypes: " + ex);
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    System.err.println("Eroare finally getTreeTypes: " + e);
                }
            }
        }
    }

    @Deprecated
    public int addNewTreeNode(TreeNodeModelDto node, TreeNodeModelDto currentNode) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);


            String addNode = "INSERT INTO "
                    + "TRE "
                    + "(ID , PID , NME , DSC , TRE_TPE , TEL1 , TEL2 , TEL3 , FVR , VLD_FRM , VLD_TO) "
                    + "VALUES (TRE_SEQ.NEXTVAL , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)";

            ps = con.prepareStatement(addNode);

            ps.setLong(1, currentNode.getId());
            ps.setString(2, node.getName());
            ps.setString(3, node.getDescription());
          //  ps.setInt(4, node.getTpeId());
            ps.setString(5, node.getPhoneNo());
            /*ps.setString(6, node.getPhoneNo2());
            ps.setString(7, node.getPhoneNo3());*/
            ps.setDouble(8, node.getPosition());
            /*ps.setDate(9, new java.sql.Date(node.getValidateFrom().getTime()));
            ps.setDate(10, node.getValidateTo() == null ? null : new java.sql.Date(node.getValidateTo().getTime()));*/

            ps.executeUpdate();

            con.commit();
            ResultSet generatedKeys = ps.getGeneratedKeys();
            if (generatedKeys.next()) {
                return generatedKeys.getInt(1);
            }
            generatedKeys.close();
        } catch (Exception e) {
            System.err.println("Eroare addNode:" + e);
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex) {
                System.err.println("[addNode] Eroor:" + ex);
            }
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                System.err.println("[addNode] finally Eroor:" + ex);
            }
        }
        return -1;
    }

    @Deprecated
    public boolean updateNode(TreeNodeModelDto node, TreeNodeModelDto curentNode) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);

            String query = "UPDATE TRE SET NME = ? , DSC = ? , TEL1 = ? , TEL2 = ? , TEL3 = ? , TRE_TPE = ? , VLD_FRM = ? , VLD_TO = ? , FVR = ? WHERE ID = " + curentNode.getId();
            pstmt = con.prepareStatement(query);

            pstmt.setString(1, node.getName());
            pstmt.setString(2, node.getDescription());
            pstmt.setString(3, node.getPhoneNo());

      //      pstmt.setInt(6, node.getTpeId());
            /*pstmt.setDate(7, new java.sql.Date(node.getValidateFrom().getTime()));
            pstmt.setDate(8, node.getValidateTo() == null ? null : new java.sql.Date(node.getValidateTo().getTime()));*/
            pstmt.setDouble(9, node.getPosition());

            pstmt.executeUpdate();

            con.commit();

            return true;
        } catch (Exception e) {
            System.err.println("Eroare updateNode:" + e);
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex) {
                System.err.println("[updateNode] Eroor:" + ex);
            }
            return false;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                System.err.println("[updateNode] finally Eroor:" + ex);
            }
        }
    }

    @Deprecated
    public TreeNodeModelDto getTreeNode(long id) {
        Connection con = null;
        try {
            String query = "select ID, NME , DSC , TRE_TPE , TEL1 , TEL2 , TEL3 , VLD_FRM , VLD_TO , FVR from tre where id = " + id;
            con = dataSource.getConnection();
            PreparedStatement ps = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            TreeNodeModelDto nod = new TreeNodeModelDto();
            while (rs.next()) {
                nod.setId(rs.getInt("ID"));
                nod.setName(rs.getString("NME"));
                nod.setDescription(rs.getString("DSC"));
       //         nod.setTpeId(rs.getInt("TRE_TPE"));
                nod.setPhoneNo(rs.getString("TEL1"));

                /*nod.setValidateFrom(rs.getDate("VLD_FRM"));
                nod.setValidateTo(rs.getDate("VLD_TO"));*/
                nod.setPosition(rs.getDouble("FVR"));
            }
            return nod;
        } catch (Exception ex) {
            System.err.println("Eroare getNodeNmeAndDsc: " + ex);
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    System.err.println("Eroare finally getNodeNmeAndDsc: " + e);
                }
            }
        }
    }

    private void addAllParents(TreeNode tn, Set<Long> selectedNodesIds) {
        if (tn.getParent() != null) {
            selectedNodesIds.add(((TreeNodeModelDto) tn.getData()).getId());
            addAllParents(tn.getParent(), selectedNodesIds);
        }
    }

    public void saveRoleTreeNodes(TreeNode[] selectedTreeNodes, Set<Long> canWriteNodeIds, long groupId) {
        Set<Long> selectedNodesIds = new HashSet<Long>();
        for (TreeNode nod : selectedTreeNodes) {
            selectedNodesIds.add(((TreeNodeModelDto) nod.getData()).getId());
            addAllParents(nod, selectedNodesIds);
        }
        Connection con = null;
        PreparedStatement ps = null;
        PreparedStatement pstmt = null;
        try {
            String query = "DELETE FROM TREE_USER_GROUPS WHERE GROUP_ID = " + groupId;
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);
            ps.executeUpdate();
            for (Long nodId : selectedNodesIds) {
                if (nodId != 0) {
                    String sql = "INSERT INTO TREE_USER_GROUPS (TREE_ID, GROUP_ID, CAN_WRITE) VALUES (?, ?, ?)";
                    pstmt = con.prepareStatement(sql);
                    pstmt.setLong(1, nodId);
                    pstmt.setLong(2, groupId);
                    pstmt.setBoolean(3, canWriteNodeIds.contains(nodId));
                    pstmt.executeUpdate();
                }
            }

            //loadTreeForGroup();
            con.commit();
        } catch (Exception ex) {
            System.err.println("Eroare updateUserNodes: " + ex);
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex1) {
                Logger.getLogger(TreeModelDao.class.getName()).log(Level.SEVERE, null, ex1);
            }
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                System.err.println("Eroare finally updateUserNodes: " + e);
            }

        }
    }

    @Deprecated
    public void updateTreeNodesPosition(List<TreeNodeModelDto> nodes) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);

            String query = "UPDATE TRE SET FVR = ? WHERE ID = ?";
            ps = con.prepareStatement(query);

            for (TreeNodeModelDto node : nodes) {
                ps.setDouble(1, node.getPosition());
                ps.setLong(2, node.getId());
                ps.executeUpdate();
            }

            con.commit();

        } catch (Exception e) {
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex) {
                System.err.println("[updateNode] Eroor:" + ex);
            }
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                System.err.println("[updateNode] finally Eroor:" + ex);
            }
        }
    }
}
